package com.bobo.dao;

import com.alibaba.fastjson.JSON;
import com.bobo.annotations.*;
import com.bobo.db.DataSourceUtils;
import com.bobo.dto.Page;
import com.bobo.util.PagerContext;
import com.bobo.util.Utils;
import org.apache.log4j.Logger;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * Created by bobo on 16-11-13.
 */
public class BaseDaoImpl<T> implements IBaseDao<T> {


    private Connection conn;

    private PreparedStatement ps;

    private ResultSet rs;

    private Class<?> clz;

    private static Logger logger = Logger.getLogger(BaseDaoImpl.class);

    /**
     * 获得连接对象
     */
    private Connection getConn() {

        if (conn == null) {
            try {
                conn = DataSourceUtils.getConn();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return conn;
    }

    /**
     * 获取对象类类型
     *
     * @return clz
     */
    private Class<?> getClz() {
        if (clz == null) {
            clz = ((Class<?>)
                    (((ParameterizedType) (this.getClass().getGenericSuperclass())).getActualTypeArguments()[0]));
        }
        return clz;
    }


    /**
     * 获取数据库表名
     *
     * @param clz 类类型
     * @return 表名
     */
    private String getTable(Class clz) {
        String table = null;
        if (clz.isAnnotationPresent(Table.class)) {
            Table annotation = (Table) clz.getAnnotation(Table.class);
            table = annotation.value();
        }
        return table;
    }


    private void close() {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 实体类中的实体类的key值
     *
     * @param obj 对象
     * @return 主键的值
     * @throws NoSuchMethodException     异常
     * @throws InvocationTargetException 异常
     * @throws IllegalAccessException    异常
     */
    private Object getKeyValue(Object obj) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        Object o = null;
        for (Field field : obj.getClass().getDeclaredFields()) {
            if (field.isAnnotationPresent(Id.class)) {
                o = getValue(obj, field.getName());
                break;
            }
        }
        return o;
    }

    /**
     * 根据名称获取对应属性的值
     *
     * @param obj  需要取值的对象
     * @param name column 名称
     * @return 对应对象的值
     * @throws NoSuchMethodException     异常
     * @throws InvocationTargetException 异常
     * @throws IllegalAccessException    异常
     */

    private Object getValue(Object obj, String name) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        Method m = obj.getClass().getDeclaredMethod("get" + Utils.captureName(name));
        return m.invoke(obj);
    }

    /**
     * 添加数据并返回主键
     *
     * @return 主键
     */
    public Integer create(Object obj) {

        Integer id = null;
        try {
            Class clzz = obj.getClass();

            StringBuilder columns = new StringBuilder();
            StringBuilder values = new StringBuilder();
            columns.append("( ");
            values.append("( ");
            List<Object> objs = new ArrayList<>();
            StringBuilder sql = new StringBuilder();

            Field[] fields = clzz.getDeclaredFields();

            for (Field field : fields) {

                // 如果主键不是自增长 需要处理
                if (field.isAnnotationPresent(Id.class)) continue;
                String colName = field.getName();
                if (field.isAnnotationPresent(Column.class)) {
                    Column col = field.getAnnotation(Column.class);
                    colName = col.value();
                }

                Object o = getValue(obj, field.getName());
                if (o != null) {
                    if (Utils.isModel(field)) {
                        Object key = getKeyValue(o);
                        if (key != null) {
                            columns.append(colName);
                            columns.append(", ");
                            values.append("? ,");
                            objs.add(key);
                        }
                    } else {
                        columns.append(colName);
                        columns.append(", ");
                        values.append("? ,");
                        objs.add(o);
                    }
                }
            }

            columns.deleteCharAt(columns.lastIndexOf(","));
            values.deleteCharAt(values.lastIndexOf(","));

            columns.append(")");
            values.append(")");

            sql.append("INSERT INTO ");
            sql.append(getTable(clzz)).append(" ");
            sql.append(columns.toString());
            sql.append(" values ");
            sql.append(values.toString());

            conn = this.getConn();

            ps = conn.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < objs.size(); i++) {
                ps.setObject(i + 1, objs.get(i));
            }
            ps.execute();
            rs = ps.getGeneratedKeys();
            rs.next();
            id = rs.getInt(1);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }

        return id;

    }

    /**
     * 删除单条表中数据
     *
     * @param id id
     * @return 是否删除数据
     */
    @Override
    public Boolean delete(Integer id) {
        String table = getTable(getClz());
        String sql = "DELETE FROM " + table + " WHERE id=?";
        int row = executeSql(sql, id);
        return row > 0;
    }


    /**
     * 更新
     *
     * @param obj 被更新对象
     * @return 是否修改成功
     */
    public boolean update(Object obj, String where, Object... objects) {
        try {
            StringBuilder sql = new StringBuilder();
            Class<?> clzz = obj.getClass();
            sql.append("UPDATE ");
            sql.append(getTable(clzz)).append(" set ");

            List<Object> objs = new ArrayList<>();
            StringBuilder sb = new StringBuilder();

            for (Field field : clzz.getDeclaredFields()) {
                String columnName = field.getName();
                if (field.isAnnotationPresent(Column.class)) {
                    Column annotation = field.getAnnotation(Column.class);
                    columnName = annotation.value();
                }

                Object o = getValue(obj, field.getName());
                if (o != null) {
                    if (Utils.isModel(field)) {
                        Object keyValue = getKeyValue(o);
                        if (keyValue != null) {
                            sql.append(columnName).append("= ?, ");
                            objs.add(keyValue);
                        }
                    } else {
                        sql.append(columnName).append("= ?, ");

                        objs.add(getValue(obj, field.getName()));
                    }
                }

                if (where == null && field.isAnnotationPresent(Id.class)) {
                    sb.append(" where ");
                    sb.append(columnName);
                    sb.append(" = ?");
                }

            }

            sql.deleteCharAt(sql.lastIndexOf(","));
            sql.append(" ");
            if (where == null) {
                sql.append(sb.toString());
                objs.add(getKeyValue(obj));
            } else {
                sql.append(where);
                objs.addAll(Arrays.asList(objects));
            }
            int row = executeSql(sql.toString(), objs.toArray());
            return row > 0;
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
        return false;

    }


    public boolean update(Object obj) {
        return this.update(obj, null);
    }

    /**
     * 通过id 返回查询对象
     *
     * @param where (  and t_user.id=?)
     * @param obj   对象
     * @return T getObj
     */
    public T getObj(String where, Object... obj) {
        String sql = createSql(getClz()) + where;
        Object o = null;
        ResultSet rs = getRs(sql, obj);
        List<Object> objects = set(rs, getClz());
        if (objects != null && objects.size() > 0) {
            o = objects.get(0);
        }

        return (T) o;
    }

    /**
     * 返回List查询对象
     * * @param where  ( and ... order by ...)
     *
     * @param obj 对象
     * @return List
     */
    public List<T> getObjs(String where, Object... obj) {
        String sql = createSql(getClz()) + where;
        ResultSet rs = getRs(sql, obj);
        return (List<T>) set(rs, getClz());
    }

    /**
     * 获取分页的list
     * * @param where  (where t_user.person_id=t_person.id and ... order by ...)
     *
     * @param obj 对象
     * @return page对象
     */
    public Page<T> getPagers(String where, Object... obj) {
        StringBuilder sql = new StringBuilder();
        sql.append(createSql(getClz()));
        sql.append(where);

        Integer count = getCount(where, obj);
        Integer current = PagerContext.getCurrent();
        Integer pageSize = PagerContext.getPageSize();
        if (pageSize == null || pageSize < 0) {
            pageSize = 15;
        }
        if (current == null || current < 0) {
            current = 0;
        }
        int countPage = count % pageSize != 0 ? (count / pageSize + 1) : (count / pageSize);

        sql = new StringBuilder(setPagers(sql.toString(), current, pageSize));
        ResultSet rs = getRs(sql.toString(), true, obj);

        Page<T> page = new Page<>();
        page.setPageSize(pageSize);
        page.setCurrent(current);
        page.setPageCount(countPage);
        page.setList((List<T>) set(rs, getClz()));
        return page;
    }

    /**
     * 通过原生sql获取查询结果
     * @param sql  sql 中三个关键子大写（SELECT   FROM   WHERE）
     * @param cl 返回的类
     * @return 返回对象
     */
    public Object getObj(String sql, Class cl, Object... obj) {
        String sq = analyzeSql(sql,cl);
        ResultSet rs = getRs(sq, obj);
        List<Object> objs = set(rs, cl);
        Object o = new Object();
        if (objs != null && objs.size() > 0) {
            o = objs.get(0);
        }
        return o;
    }

    /**
     * @param sql sql
     * @return 原生sql改装
     */
    private String analyzeSql(String sql,Class cl) {
        StringBuilder sq = new StringBuilder();
        String sf = sql.substring(sql.indexOf("SELECT") + 6, sql.indexOf("FROM"));
        String[] sp = sf.split(",");
        for (int i = 0; i < sp.length; i++) {
            String s = containsAsName(sql,cl,sp[i]);
            sq.append(s).append(",");
        }
        sq.deleteCharAt(sq.lastIndexOf(","));
        String s = sql.replaceAll(sf, sq.toString());
        return s;
    }

    /**
     * @param sql    sql
     * @param select tu.name
     * @return tu.name as t_user_id
     */
    private String containsAsName(String sql, Class cl, String select) {
        String sl = "";
        String fw = "";
        if(sql.contains("WHERE")) fw = sql.substring(sql.indexOf("FROM")+4, sql.indexOf("WHERE"));
        else fw = sql.substring(sql.indexOf("FROM")+4);
        String[] split = fw.split(",");
        for (String s : split) {
            String[] split1 = s.split("\\s+");
            String tableName = split1[1];
            String asName = split1[2];
            if (select.contains(asName) && select.contains("*")) return modelSql(tableName, cl);
            if (select.contains(asName)) {
                String[] selc = select.split("\\.");
                System.out.println(JSON.toJSONString(selc));
                sl = select + " As " + tableName + "_" + selc[1];
                return sl;
            }
        }
        return sl;
    }

    /**
     * 将sql中的*ti替换成具体项
     * @param tableName 表名
     * @param cl 查询返回的对象类
     * @return 替换后的sql
     */
    private String modelSql(String tableName, Class cl) {
        StringBuffer selc = new StringBuffer();
        for (Field field : cl.getDeclaredFields()) {
            if (!Utils.isModel(field)) continue;
            if (!field.getType().isAnnotationPresent(Table.class)) continue;
            String value = field.getType().getAnnotation(Table.class).value();
            if (!tableName.equals(value)) continue;
            for (Field f : field.getType().getDeclaredFields()) {
                String columnName = f.getName();
                if (f.isAnnotationPresent(Column.class)) columnName = f.getAnnotation(Column.class).value();
                selc.append(tableName).append(".").append(columnName).append(" as ")
                        .append(tableName).append("_").append(columnName).append(",");
            }
        }
        return selc.toString();
    }

    /**
     * 生成视图sql
     * @param where  条件语句
     * @param cl  dto类类型
     * @return dtosql
     */
    private String dtoSql(String where, Class cl) {
        StringBuffer dtoSql = new StringBuffer();
        dtoSql.append("select ");
        dtoSql.append(selectSql(cl,true));
        dtoSql.deleteCharAt(dtoSql.lastIndexOf(","));
        dtoSql.append(" from ");
        dtoSql.append(getTable(cl));
        dtoSql.append(where);
        return  dtoSql.toString();
    }

    public Object queryDto(String where, Class cl ,Object...obj){
        String sql = dtoSql(where,cl);
        Object o = null;
        ResultSet rs = getRs(sql, obj);
        List<Object> objects = set(rs, cl);
        if (objects != null && objects.size() > 0) {
            o = objects.get(0);
        }
        return o;
    }

    /**
     * 查找内容的sql
     * @param cl   dto类类型
     * @return  selectSql
     */
    private String selectSql(Class cl,boolean isAll){
        StringBuffer sql = new StringBuffer();
        String tableName = getTable(cl);
        Field[] fields = cl.getDeclaredFields();
        for(Field field : fields){
            String fieldName = field.getName();
            String columnName = fieldName;
            if (field.isAnnotationPresent(Column.class)) {
                Column column = field.getAnnotation(Column.class);
                columnName = column.value();
            }
            if(Utils.isModel(field) && isAll){
                sql.append(selectSql(field.getType(),false));
                continue;
            }
            if(Utils.isModel(field)) continue;
            if(isAll){
                sql.append(tableName).append(".").append(columnName)
                        .append(" as ")
                        .append(tableName).append("_").append(fieldName).append(",");
            }else {
                sql.append(tableName).append("_").append(fieldName).append(",");
            }

        }
        return sql.toString();
    }

    /**
     * 返回查询的rs集
     * @param sql sql
     * @param obj 对象
     * @return rs
     */
    private ResultSet getRs(String sql, Object... obj) {
        try {
            Connection conn = this.getConn();
            ps = conn.prepareStatement(sql);
            if (obj != null && obj.length > 0) {
                for (int i = 0; i < obj.length; i++) {
                    ps.setObject(i + 1, obj[i]);
                }
            }
            rs = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }


    /**
     * 通过类类型生成sql语句  不带where条件
     *
     * @param clazz 类类型
     * @return 拼接的查询sql
     */
    private String createSql(Class clazz) {
        StringBuilder sql = new StringBuilder();
        sql.append("select ");
        StringBuilder tables = new StringBuilder();
        StringBuilder foreign = new StringBuilder();

        this.createSelectSql(clazz, foreign, tables, sql, false);

        sql.deleteCharAt(sql.lastIndexOf(","));
        sql.append(" from ");
        sql.append(tables.toString());
        sql.deleteCharAt(sql.lastIndexOf(","));
        sql.append("where 1 = 1 ");

        if (foreign.length() > 0) {
            sql.append(" and ");
            foreign.delete(foreign.lastIndexOf("a"), foreign.length() - 1);

            sql.append(foreign.toString());

        }
        return sql.toString();
    }


    private void createSelectSql(Class clzz, StringBuilder foreign, StringBuilder tables, StringBuilder sql, boolean onlyKey) {
        Field[] fields = clzz.getDeclaredFields();
        String tableName = getTable(clzz);
        tables.append(tableName);
        tables.append(" ,");

        for (Field field : fields) {
            String fieldName = field.getName();
            String columnName = fieldName;
            if (field.isAnnotationPresent(Column.class)) {
                Column column = field.getAnnotation(Column.class);
                columnName = column.value();
            }

            Class clazz = field.getType();
            if (Utils.isCollection(clazz)) continue;

            //表关系 后半截
            if (foreign.length() > 0 && !foreign.toString().trim().endsWith(",") && field.isAnnotationPresent(Id.class)) {
                foreign.append(" = ");
                foreign.append(tableName);
                foreign.append(".");
                foreign.append(columnName);
                foreign.append(" and ");
            }
            //没有ManyToOne 时只取id
            if (onlyKey) {
                if (field.isAnnotationPresent(Id.class)) {
                    sql.append(tableName).append(".").append(columnName)
                            .append(" as ")
                            .append(tableName).append("_").append(fieldName).append(",");
                    break;
                }
                continue;
            }
            if (Utils.isModel(field)) {
                //表关系 前半截
                //判断是否有ManyToOne 有: 取所有属性值
                if (field.isAnnotationPresent(ManyToOne.class)) {
                    foreign.append(tableName);
                    foreign.append(".");
                    foreign.append(columnName);
                    createSelectSql(clazz, foreign, tables, sql, false);
                } else if (!field.isAnnotationPresent(Transient.class)) {
                    foreign.append(tableName);
                    foreign.append(".");
                    foreign.append(columnName);
                    createSelectSql(clazz, foreign, tables, sql, true);
                }
            } else {
                sql.append(tableName).append(".").append(columnName)
                        .append(" as ")
                        .append(tableName).append("_").append(fieldName).append(",");
            }
        }
    }

    /**
     * rs 组装对象
     *
     * @param rs rs
     * @return 组装后的对象
     */
    private List<Object> set(ResultSet rs, Class clz) {
        List<Object> obis = new ArrayList<>();
        try {
            while (rs.next()) {
                Object obj = assembleObj(rs, clz);
                obis.add(obj);
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
        } finally {
            close();
        }
        return obis;
    }

    private Object assembleObj(ResultSet rs, Class clzzz) throws IllegalAccessException, InstantiationException, SQLException, NoSuchMethodException, InvocationTargetException {

        Object obj = clzzz.newInstance();
        Field[] fields = clzzz.getDeclaredFields();
        for (Field field : fields) {
            if (Utils.isModel(field) && !field.isAnnotationPresent(ManyToOne.class)) continue;
            String rsName = getTable(clzzz) + "_" + field.getName();
            Object type = null;
            if (!Utils.isModel(field) && !Utils.isCollection(field.getType())) {
                type = getType(rs, field.getType(), rsName);
            }
            Method method = clzzz.getDeclaredMethod("set" + Utils.captureName(field.getName()), field.getType());

            if (Utils.isModel(field)) {
                Object o = assembleObj(rs, field.getType());
                method.invoke(obj, o);
            } else if (!Utils.isCollection(field.getType())) {
                method.invoke(obj, type);
            }
        }
        return obj;
    }

    /**
     * rs  get结果
     *
     * @param clz    类型
     * @param rsName 查询结果名成
     * @return 对象
     * @throws SQLException 异常
     */
    private Object getType(ResultSet rs, Class clz, String rsName) throws SQLException {
        Object o = null;
        if (isExistColumn(rs, rsName)) {
            switch (clz.toString()) {
                case ("class java.lang.String"):
                    o = rs.getString(rsName);
                    break;
                case ("class java.lang.Integer"):
                    o = rs.getInt(rsName);
                    break;
                case ("class java.util.Date"):
                    o = rs.getDate(rsName);
                    break;
                case ("class java.util.Timestamp"):
                    o = rs.getTimestamp(rsName);
                    break;
                case ("class java.sql.Timestamp"):
                    o = rs.getTimestamp(rsName);
                    break;
            }
        }
        return o;
    }

    /**
     * 判断查询结果集中是否存在某列
     *
     * @param rs     查询结果集
     * @param rsName 列名
     * @return true 存在; false 不存咋
     */
    private boolean isExistColumn(ResultSet rs, String rsName) {
        try {
            if (rs.findColumn(rsName) > 0) {
                return true;
            }
        } catch (SQLException e) {
            return false;
        }

        return false;
    }

    /**
     * 获取分页列表集合
     *
     * @param sql 条件语句
     * @return sql
     */
    public String setPagers(String sql, Integer current, Integer pageSize) {

        StringBuilder sb = new StringBuilder();

        sb.append(sql);
        if (current > 0 && pageSize > 0) {
            sb.append(" limit ");
            sb.append((current - 1) * pageSize);
            sb.append(",");
            sb.append(pageSize);
        }
        return sb.toString();
    }

    /**
     * 不带条件查询表中数据总数
     *
     * @return 表中数据记录数量
     */
    @Override
    public Integer getCount() {
        String table = getTable(getClz());
        String sql = "Select count(*) from " + table;
        return getPageCount(sql);
    }

    /**
     * 带条件查询表中数据总数
     *
     * @param where 条件
     * @param obj   obj
     * @return 表中数据记录数量
     */
    @Override
    public Integer getCount(String where, Object... obj) {
        String table = getTable(getClz());
        String sql = "Select count(*) from " + table + " where 1=1 " + where;
        return getPageCount(sql, obj);
    }

    /**
     * 查询数据记录总数
     *
     * @param sql sql
     * @param obj 条件
     * @return Integer pageNum
     */
    private Integer getPageCount(String sql, Object... obj) {

        Integer pageNum = 0;
        Connection conn = getConn();
        PreparedStatement ps;
        ResultSet rs;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                ps.setObject(i + 1, obj[i]);
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                pageNum = rs.getInt(1);
            }

        } catch (SQLException e) {
            logger.error(e.getMessage());
        } finally {
            close();
        }
        return pageNum;
    }


    /**
     * 获取数据库操作影响的行数
     *
     * @param sql  sql
     * @param objs 参数
     * @return row
     */
    private int executeSql(String sql, Object... objs) {
        Connection conn = getConn();
        int row = 0;
        PreparedStatement ps;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < objs.length; i++) {
                ps.setObject(i + 1, objs[i]);
            }
            row = ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }

        return row;
    }

}
